package org.zwd.computePrice.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.zwd.computePrice.domain.Crab;
import org.zwd.computePrice.domain.CrabPrice;
import org.zwd.computePrice.domain.Temp;
import org.zwd.computePrice.domain.User;
import org.zwd.computePrice.util.DateUtils;


public class TempDao {
	public void insertTempItem(Connection conn,int tempid,Crab crab){
		PreparedStatement pstmt = null;
		try {
			String sql = "insert into cp_temp_crab(tempid,crabid,price,number,note) values(?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tempid);
			pstmt.setInt(2, crab.getId());
			pstmt.setDouble(3, crab.getCrabPrice().getPrice());
			pstmt.setDouble(4, crab.getNumber());
			pstmt.setString(5, crab.getNote());
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	public int insertTemp(Connection conn,Temp temp){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int num = -1;
		try {
			String sql = "insert into cp_temp(time,clientName,userid,isOrder) values(?,?,?,?)";
			pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			pstmt.setString(2, temp.getClientName());
			pstmt.setInt(3, temp.getUser().getId());
			pstmt.setInt(4, temp.getIsOrder());
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			if(rs.next()){
				num = rs.getInt(1);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, null);
		}
		return num;
	}
	
	public List<Temp> findAllTemp(int isOrder){
		List<Temp> tempList = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			tempList = new ArrayList<Temp>();
			conn = DBUtils.getConnection();
			String sql = "select id,time,clientName,userid,isOrder from cp_temp where isOrder=? order by time desc";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, isOrder);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Temp temp = new Temp();
				temp.setId(rs.getInt(1));
				temp.setDate(DateUtils.date2String(rs.getDate(2)));
				temp.setClientName(rs.getString(3));
				User user = new UserDao().findUserByid(rs.getInt(4));
				temp.setUser(user);
				temp.setIsOrder(rs.getInt(5));
				tempList.add(temp);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return tempList;
	}
	
	public Temp findTempByid(int id){
		Temp temp = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtils.getConnection();
			String sql = "select id,time,clientName,userid,isOrder from cp_temp where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				temp = new Temp();
				temp.setId(rs.getInt(1));
				temp.setDate(DateUtils.date2String(rs.getDate(2)));
				temp.setClientName(rs.getString(3));
				User user = new UserDao().findUserByid(rs.getInt(4));
				temp.setUser(user);
				temp.setIsOrder(rs.getInt(5));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return temp;
	}
	
	public List<Crab> findCrabByTempId(int tempid){
		CrabDao crabDao = new CrabDao();
	
		List<Crab> crabList = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			crabList = new ArrayList<Crab>();
			conn = DBUtils.getConnection();
			String sql = "select crabid,price,number,note from cp_temp_crab where tempid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tempid);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				Crab crab = crabDao.findCrabById(rs.getInt(1));
				CrabPrice crabPrice = new CrabPrice();
				crabPrice.setPrice(rs.getDouble(2));
				crab.setCrabPrice(crabPrice);
				crab.setNumber(rs.getDouble(3));
				crab.setNote(rs.getString(4));
				crabList.add(crab);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return crabList;
	}
	
	public void deleteOrderByid(Connection conn,int tempid ){
		PreparedStatement pstmt = null;
		try {
			String sql = "delete from cp_temp where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tempid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	public void deleteOrderItemByid(Connection conn,int tempid){
		PreparedStatement pstmt = null;
		try {
			String sql = "delete from cp_temp_crab where tempid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tempid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
}
